using Excel;
using OfficeOpenXml;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using UnityEditor;
using UnityEngine;

namespace Super
{
	public static class ExcelUtil
	{
		/// <summary>
		/// 获取表格基本信息
		/// </summary>
		/// <param name="excelPath">表格路径</param>
		/// <param name="sheetIndex">第几张表   从0开始</param>
		/// <returns></returns>
		public static ExcelTableInfo GetTableInfo(string excelPath, int sheetIndex)
		{
			string excelAbsolutePath = GlobalUtil.GetAbsolutePath(excelPath);
			ExcelTableInfo tableInfo = GetExcelTableData(excelPath, sheetIndex);
			string tableName = GetExcelTableNames(excelAbsolutePath)[sheetIndex];
			tableInfo.hasKey = false;
			tableInfo.hasLang = false;
			tableInfo.fieldNum = tableInfo.col - tableInfo.ignoreCol;
			tableInfo.remarks = FormatRemarks(tableInfo.data[0].Skip(tableInfo.ignoreCol).ToArray());
			tableInfo.fieldNames = tableInfo.data[1].Skip(tableInfo.ignoreCol).ToArray();
			tableInfo.fieldTypes = tableInfo.data[2].Skip(tableInfo.ignoreCol).ToArray();
			tableInfo.nodeName = tableName + "Node";
			tableInfo.tableName = tableName + "Table";
			tableInfo.scriptPath = string.Format(PathSettingSO.Ins.excelScriptPath, tableInfo.tableName);
			tableInfo.excelPath = excelPath;


			HandleSpecialField(tableInfo);

			return tableInfo;
		}

		/// <summary>
		/// 获取Excel所有表的名称
		/// </summary>
		/// <param name="path">Excel的文件路径</param>
		/// <returns></returns>
		public static List<string> GetExcelTableNames(string path)
		{
			path = GlobalUtil.GetAbsolutePath(path);
			FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
			IExcelDataReader excelRead = ExcelReaderFactory.CreateOpenXmlReader(stream);
			DataSet result = excelRead.AsDataSet();
			List<string> names = new List<string>();
			for (int i = 0; i < result.Tables.Count; i++)
			{
				names.Add(result.Tables[i].TableName);
			}
			return names;
		}

		/// <summary>
		/// 格式化备注内容
		/// </summary>
		/// <param name="remarksArg"></param>
		/// <returns></returns>
		public static string[] FormatRemarks(string[] remarksArg)
		{
			string[] remarks = new string[remarksArg.Length];
			for (int i = 0; i < remarksArg.Length; i++)
			{

				remarks[i] = remarksArg[i].Replace("\n", "|");
			}
			return remarks;
		}

		private static ExcelTableInfo GetExcelTableData(string path, int sheetIndex)
		{
			FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
			IExcelDataReader excelRead = ExcelReaderFactory.CreateOpenXmlReader(stream);
			DataSet result = excelRead.AsDataSet();
			DataRowCollection collect = result.Tables[sheetIndex].Rows;
			int col = result.Tables[sheetIndex].Columns.Count;
			int row = result.Tables[sheetIndex].Rows.Count;
			int offsetCol = 1;
			int offsetRow = 2;
			string[][] data;
			while (row > 0)
			{
				if (collect[row - 1][offsetCol].ToString() == "")
				{
					row--;
					continue;
				}
				break;
			}
			while (col > 0)
			{
				if (collect[offsetRow][col - 1].ToString() == "")
				{
					col--;
					continue;
				}
				break;
			}

			data = new string[row][];
			for (int i = 0; i < row; i++)
			{
				data[i] = new string[col];
				for (int j = 0; j < col; j++)
				{
					data[i][j] = collect[i][j].ToString();
				}
			}
			ExcelTableInfo info = new ExcelTableInfo();
			info.data = data;
			info.row = row;
			info.col = col;
			info.ignoreRow = 3;
			info.ignoreCol = offsetCol;
			return info;
		}

		private static void HandleSpecialField(ExcelTableInfo tableInfo)
		{
			for (int i = 0; i < tableInfo.fieldNum; i++)
			{
				string fieldName = tableInfo.fieldNames[i];
				if (fieldName.Contains("[K]") || fieldName.Contains("[k]"))
				{
					tableInfo.hasKey = true;
					fieldName = fieldName.Replace("[K]", "");
					fieldName = fieldName.Replace("[k]", "");

					tableInfo.keys.Add(fieldName);
					tableInfo.keyTypes.Add(tableInfo.fieldTypes[i]);
				}

				if (fieldName.Contains("[L]") || fieldName.Contains("[l]"))
				{
					tableInfo.hasLang = true;
					fieldName = fieldName.Replace("[L]", "");
					fieldName = fieldName.Replace("[l]", "");

					tableInfo.langFieldNames.Add(fieldName);
					tableInfo.langFieldIndexs.Add(i);
				}

				tableInfo.fieldNames[i] = fieldName;
			}
		}

		public static void WriteExcel(string excelPath)
		{
			ExcelNoteNode node = ExcelNoteSO.Ins.FindTableInfo(excelPath);
			for (int i = 0; i < node.tableNames.Count; i++)
			{
				WriteSOToExcel(node, i);
			}
			Debug.Log("覆写" + excelPath + "文件");
		}

		/// <summary>
		/// 使用SO的内容，并覆写Excel文件
		/// LookAt ExcelPackage 里面的数组从1开始：坑啊
		/// </summary>
		/// <param name="node"></param>
		/// <param name="sheetIndex"></param>
		public static void WriteSOToExcel(ExcelNoteNode node, int sheetIndex)
		{
#if UNITY_EDITOR
			string excelAbsolutePath = GlobalUtil.GetAbsolutePath(node.excelPath);
			FileInfo excelFile = new FileInfo(excelAbsolutePath);
			ExcelPackage package = new ExcelPackage(excelFile);
			ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetIndex + 1];
			ExcelTableInfo info = ExcelUtil.GetTableInfo(node.excelPath, sheetIndex);
			Type typeSO = ExcelNoteSO.Ins.GetTableClassType(node, sheetIndex);

			var excelAsset = AssetDatabase.LoadAssetAtPath(node.SOPaths[sheetIndex], typeSO);
			var data = typeSO.GetField("data").GetValue(excelAsset);
			IList dataList = (IList)data;
			string[] rowData;
			for (int i = 0; i < dataList.Count; i++)
			{
				var item = dataList[i];
				item.GetType().GetMethod("ValueBackFill").Invoke(item, null);
				rowData = (string[])item.GetType().GetField("rowData").GetValue(item);
				for (int j = 0; j < rowData.Length; j++)
				{
					worksheet.SetValue(i + info.ignoreRow + 1, j + info.ignoreCol + 1, rowData[j]);
				}
			}
			//修改某一格的数据
			//worksheet.Cells[4, 3].Value = "女";
			//worksheet.SetValue(4, 3, "女");
			//保存excel
			package.Save();
#endif
		}

		/// <summary>
		/// 读取Excel表格数据，并存储到SO中
		/// </summary>
		/// <param name="node"></param>
		/// <param name="sheetIndex"></param>
		public static void ReadExcelToSO(ExcelNoteNode node, int sheetIndex)
		{
#if UNITY_EDITOR
			Type typeSO = ExcelNoteSO.Ins.GetTableClassType(node, sheetIndex);
			Type typeData = ExcelNoteSO.Ins.GetTableNodeType(node, sheetIndex);
			ExcelTableInfo info = ExcelUtil.GetTableInfo(node.excelPath, sheetIndex);
			var excelAsset = AssetDatabase.LoadAssetAtPath(node.SOPaths[sheetIndex], typeSO);
			if (excelAsset == null)
			{
				excelAsset = ScriptableObject.CreateInstance(typeSO);
				AssetUtil.TryCreateDirectory(node.SOPaths[sheetIndex]);
				AssetDatabase.CreateAsset(excelAsset, node.SOPaths[sheetIndex]);
				string groupName = ValueSettingSO.Ins.excelCfgAGroup;
				AssetUtil.CreateOrMoveEntry(groupName, node.SOPaths[sheetIndex]);
				AssetDatabase.SaveAssets();
				AssetDatabase.Refresh();
			}

			typeSO.GetField("fieldNames").SetValue(excelAsset, info.fieldNames);
			typeSO.GetField("fieldTypes").SetValue(excelAsset, info.fieldTypes);
			typeSO.GetField("keys").SetValue(excelAsset, info.keys.ToArray());
			typeSO.GetField("keyTypes").SetValue(excelAsset, info.keyTypes.ToArray());
			typeSO.GetField("excelPath").SetValue(excelAsset, info.excelPath);
			typeSO.GetMethod("Clear").Invoke(excelAsset, null);
			MethodInfo typeSO_AddNodeData = typeSO.GetMethod("AddNodeData");

			string[][] allRowData = info.data.Skip(info.ignoreRow).ToArray();
			foreach (var rowData in allRowData)
			{
				string[] data = rowData.Skip(info.ignoreCol).ToArray();

				var obj = Activator.CreateInstance(typeData);
				typeData.GetMethod("SetValue").Invoke(obj, new object[] { data });
				typeSO_AddNodeData.Invoke(excelAsset, new object[] { obj });
			}

			if (info.hasKey)
			{
				MethodInfo typeSO_SaveExcelDictionary = typeSO.GetMethod("SaveExcelDictionary");
				typeSO_SaveExcelDictionary.Invoke(excelAsset, null);
			}

			if (info.hasLang)
			{
				HashSet<string> langSet = LangUtil.GetExcelNewKeys(info);
				LangUtil.AddKeys(langSet, PathSettingSO.Ins.excelLangKeyPath);
				Debug.Log($"{node.excelPath}_{node.tableNames[sheetIndex]} 表格多语言处理");
			}

			EditorUtility.SetDirty(excelAsset);
#endif
		}

		public static void LoadExcel(string excelPath)
		{
			string relativePath = GlobalUtil.GetAssetsRelativePath(excelPath);
			if (string.IsNullOrEmpty(excelPath))
			{
				Debug.Log("Excel路径不能为空");
				return;
			}

			ExcelNoteNode node = ExcelNoteSO.Ins.FindTableInfo(relativePath);
			for (int i = 0; i < node.tableNames.Count; i++)
			{
				ReadExcelToSO(node, i);
			}
			Debug.Log("导入" + excelPath + "表格");
		}

		public static void LoadAllExcel()
		{
			foreach (var item in ExcelNoteSO.Ins.notes)
			{
				LoadExcel(item.excelPath);
			}
			Debug.Log("重新导入所有表格");
		}
	}

}
